package org.jeecg.modules.airag.llm.config;

import org.jeecg.modules.airag.flow.component.enhance.IAiRagEnhanceJava;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class DatabaseUtil implements IAiRagEnhanceJava {

    // 数据库连接信息（请根据你的环境修改）
    private static final String URL = "jdbc:mysql://118.178.106.254:3306/hd_person_manage?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "6a29c7071ae1f1b9";



    /**
     * 方法二：执行查询 SQL 语句
     * @param sql 查询语句（支持参数化查询）
     * @param params 参数数组
     * @return 查询结果列表（Map 形式）
     */

//    public List<Map<String, Object>> executeQuery(String sql, Object... params) {
//        List<Map<String, Object>> result = new ArrayList<>();
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        ResultSet rs = null;
//
//        try {
//            conn = getConnection();
//            if (conn == null) {
//                System.err.println("❌ 无法执行查询，数据库连接为 null");
//                return result;
//            }
//
//            pstmt = conn.prepareStatement(sql);
//            System.out.println("pstmt = " + pstmt);
//            setParameters(pstmt, params);  // 设置参数
//            rs = pstmt.executeQuery();
//
//            ResultSetMetaData metaData = rs.getMetaData();
//            int columnCount = metaData.getColumnCount();
//
//            while (rs.next()) {
//                Map<String, Object> row = new HashMap<>();
//                for (int i = 1; i <= columnCount; i++) {
//                    row.put(metaData.getColumnLabel(i), rs.getObject(i));
//                }
//                result.add(row);
//            }
////            System.out.println("查询结果：" + result.get(0));
//// 替换原打印行
//            if (!result.isEmpty()) {
//                Map<String, Object> firstRow = result.get(0);
//                System.out.println("查询结果包含的字段数：" + firstRow.size());
//                // 逐个打印字段和值
//                for (Map.Entry<String, Object> entry : firstRow.entrySet()) {
//                    System.out.println(entry.getKey() + " = " + entry.getValue() + "（类型：" + (entry.getValue() == null ? "null" : entry.getValue().getClass()) + "）");
//                }
//            }
//        } catch (SQLException e) {
//            System.err.println("❌ 查询失败：" + e.getMessage());
//        } finally {
//            closeResources(rs, pstmt, conn);
//        }
//
//        return result;
//    }


    public List<Map<String, Object>> executeQuery(String sql, Object... params) {
        List<Map<String, Object>> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            if (conn == null) {
                System.err.println("❌ 无法执行查询，数据库连接为 null");
                return result;
            }

            pstmt = conn.prepareStatement(sql);
            System.out.println("执行的SQL: " + sql);
            setParameters(pstmt, params);  // 设置参数

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 收集列名信息，便于调试
            List<String> columnNames = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnLabel(i));
            }
            System.out.println("查询的列名: " + columnNames);

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>(columnCount); // 初始化指定容量
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                result.add(row);
            }

            // 打印结果统计信息
            System.out.println("查询完成，返回 " + result.size() + " 条记录");
            

        } catch (SQLException e) {
            System.err.println("❌ 查询失败: " + e.getMessage());
            e.printStackTrace(); // 打印完整堆栈跟踪，便于调试
        } finally {
            // 确保资源正确关闭，使用工具方法处理
            closeResources(rs, pstmt, conn);
        }
//        System.out.println("result = " + result);
        return result;
    }


    /**
     * 方法一：获取数据库连接
     * @return Connection 连接对象
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            // 1. 加载驱动（MySQL 8+ 驱动可省略此步骤）
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 获取连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("✅ 数据库连接成功！");
        } catch (ClassNotFoundException e) {
            System.err.println("❌ 驱动类未找到：" + e.getMessage());
        } catch (SQLException e) {
            System.err.println("❌ 数据库连接失败：" + e.getMessage());
        }
        return conn;
    }
    /**
     * 设置参数到 PreparedStatement 中
     */
    private void setParameters(PreparedStatement pstmt, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            pstmt.setObject(i + 1, params[i]);
        }
    }

    /**
     * 关闭资源
     */
    private void closeResources(ResultSet rs, PreparedStatement pstmt, Connection conn) {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null && !conn.isClosed()) conn.close();
        } catch (SQLException e) {
            System.err.println("❌ 资源关闭失败：" + e.getMessage());
        }
    }

    @Override
    public Map<String, Object> process(Map<String, Object> map) {
        // 示例：连接数据库并执行一个查询
        Connection conn = getConnection();  // 获取连接

        if (conn != null) {
            String sql = (String) map.get("sql");  // 从参数中获取 SQL
            System.out.println("sql = " + sql);
            List<Map<String, Object>> result = executeQuery(sql);


            return Map.of("result", result);  // 返回结果
        } else {
            return Map.of("error", "数据库连接失败");
        }
    }

}